﻿using System;
using System.Collections.Generic;
using System.Text;
using NLBS.Entity;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region UserGroup

        public override IList<UserGroup> GetUserGroup()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT group_id, group_name, group_rights FROM blog_UserGroup");

            IList<UserGroup> lists = new List<UserGroup>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    UserGroup group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                    lists.Add(group);
                }
            }
            return lists;
        }

        public override int InsertUserGroup(UserGroup group)
        {
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand("INSERT INTO `blog_UserGroup` (`group_name`, `group_rights`) VALUES (?, ?)");
                    db.AddInParameter(dbCommand, "group_Name", DbType.String, group.Name);
                    db.AddInParameter(dbCommand, "group_Rights", DbType.String, group.Rights);
                    db.ExecuteNonQuery(dbCommand,transaction);
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandText = "SELECT @@IDENTITY";
                    int result = (int)db.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                    return result;
                }
                catch
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                }
                connection.Close();
            }
            return 0;
        }

        public override void UpdateUserGroup(UserGroup group)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("UPDATE `blog_UserGroup` SET `group_name` = ?, `group_rights` = ? WHERE ((`group_id` = ?))");

            db.AddInParameter(dbCommand, "group_Name", DbType.String, group.Name);
            db.AddInParameter(dbCommand, "group_Rights", DbType.String, group.Rights);
            db.AddInParameter(dbCommand, "group_Id", DbType.String, group.GroupID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override UserGroup GetUserGroupByID(int groupID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT group_id, group_name, group_rights FROM blog_UserGroup WHERE   (group_id = ?)");
            db.AddInParameter(dbCommand, "group_id", DbType.Int32, groupID);

            UserGroup group = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                }
            }
            return group;
        }

        public override void DeleteUserGroup(int groupID)
        {
           
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM `blog_UserGroup` WHERE ((`group_id` = ?))");
            db.AddInParameter(dbCommand, "group_Id", DbType.String, groupID);
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
